package moldat;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;

/**
 * SQLite-Datenbankanbindung
 *
 * @author Sebastian Lindner
 */
public class SQLiteDB implements ServletContextListener {

    /**
     * Registers SQLite-Driver and sets up connection to the database-file with
     * the specified name (creates it if it doesn't exist).
     *
     * @param databaseName SQLite-Database Name
     */
    public static void initialize() throws SQLException, ClassNotFoundException {
        // register driver with DriverManager
        Class.forName("org.sqlite.JDBC");
        // create database connection
        connection = DriverManager.getConnection("jdbc:sqlite:" + ApplicationConfig.getDbPath());
        stmt = connection.createStatement();
        stmt.setQueryTimeout(ApplicationConfig.getQueryTimeout());

        // set up "protein", "gene" and "synonym" tables if necessary
        if (!hasStructure()) {
            print("Connection established but database empty - setting up structure.");
            createDatabase();
        }

        // prepare statements for better performance
        readProtein = connection.prepareStatement("SELECT * FROM protein WHERE uniProtID = ?");
        readGene = connection.prepareStatement("SELECT geneID FROM gene WHERE name = ?");
        readSynonym = connection.prepareStatement("SELECT synID FROM synonym WHERE geneID = ? AND name = ?");
        readIsoform = connection.prepareStatement("SELECT isoID FROM isoform WHERE uniProtID = ? AND name = ?");

        writeProtein = connection.prepareStatement("INSERT INTO protein (uniProtID, name, geneID) VALUES (?, ?, ?)");
        writeGene = connection.prepareStatement("INSERT INTO gene (name, arrayExpressURL) VALUES (?, ?)");
        writeSynonym = connection.prepareStatement("INSERT INTO synonym (name, geneID) VALUES (?, ?)");
        writeIsoform = connection.prepareStatement("INSERT INTO isoform (name, uniProtID) VALUES (?, ?)");

        getUniProtID = connection.prepareStatement("SELECT uniProtID FROM protein WHERE name = ?");
        getGene = connection.prepareStatement("SELECT * FROM gene WHERE geneID = ?");
        getSynonyms = connection.prepareStatement("SELECT * FROM synonym WHERE geneID = ?");
        getIsoforms = connection.prepareStatement("SELECT * FROM isoform WHERE uniProtID = ?");

        writeQuery = connection.prepareStatement("INSERT INTO queryhistory (history) VALUES (?)");
        selectQuery = connection.prepareStatement("SELECT id FROM queryhistory ORDER BY id DESC");
        deleteQuery = connection.prepareStatement("DELETE FROM queryhistory WHERE id < ?");

        //Statements for ArrayExpress
        writeOrganism = connection.prepareStatement("INSERT INTO organism (name) VALUES (?)");
        readOrganism = connection.prepareStatement("SELECT organismID FROM organism WHERE name = ?");

        writeOrganismPart = connection.prepareStatement("INSERT INTO organismpart (name) VALUES (?)");
        readOrganismPart = connection.prepareStatement("SELECT organismpartID FROM organismpart WHERE name = ?");

        writeArrayExpress = connection.prepareStatement("INSERT INTO arrayexpress (organismID, organismpartID, geneID, upPvalue, downPvalue, upExperiment, downExperiment, nonDEExperiment) VALUES (?,?,?,?,?,?,?,?)");
        readArrayExpress = connection.prepareStatement("SELECT arrayexpressID FROM arrayexpress WHERE"
                + " organismID = ? "
                + "AND organismpartID = ? "
                + "AND geneID = ?");
        // done
        print("Database located in: " + ApplicationConfig.getDbPath());
        print("Connection established to database.");
    }

    /**
     * Sets up basic structure of the database file that is currently connected.
     * If these tables already exist, they will be overwritten (data will be
     * lost).
     *
     * @throws SQLException
     */
    private static void createDatabase() throws SQLException {
        //TODO: Cursor benutzen???
        // disable autocommit for performance
        connection.setAutoCommit(false);
        // remove old tables
        ApplicationConfig.setImportDetails("Removing old Tables");
        stmt.execute("DROP TABLE IF EXISTS gene");
        stmt.execute("DROP TABLE IF EXISTS protein");
        stmt.execute("DROP TABLE IF EXISTS synonym");
        stmt.execute("DROP TABLE IF EXISTS isoform");
        stmt.execute("DROP TABLE IF EXISTS queryhistory");
        stmt.execute("DROP TABLE IF EXISTS organism");
        stmt.execute("DROP TABLE IF EXISTS organismpart");
        stmt.execute("DROP TABLE IF EXISTS arrayexpress");

        // activate foreign keys support (older versions may default to OFF)
        ApplicationConfig.setImportDetails("recreating Database");
        stmt.execute("PRAGMA foreign_keys = ON");
        // create tables
        stmt.execute("CREATE TABLE protein ("
                + "uniProtID VARCHAR PRIMARY KEY NOT NULL, "
                + "name VARCHAR, "
                + "geneID INTEGER, FOREIGN KEY (geneID) REFERENCES gene(geneID) ON DELETE CASCADE)");
        stmt.execute("CREATE TABLE gene ("
                + "geneID INTEGER PRIMARY KEY NOT NULL, "
                + "name VARCHAR, "
                + "arrayExpressURL VARCHAR)");
        stmt.execute("CREATE TABLE synonym ("
                + "synID INTEGER PRIMARY KEY NOT NULL, "
                + "name VARCHAR, "
                + "geneID INTEGER, FOREIGN KEY (geneID) REFERENCES gene(geneID) ON DELETE CASCADE)");
        stmt.execute("CREATE TABLE isoform ("
                + "isoID INTEGER PRIMARY KEY NOT NULL, "
                + "name VARCHAR, "
                + "uniProtID VARCHAR, FOREIGN KEY (uniProtID) REFERENCES protein(uniProtID) ON DELETE CASCADE)");
        //create table for queryhistory
        stmt.execute("CREATE TABLE queryhistory ("
                + "id INTEGER PRIMARY KEY NOT NULL, "
                + "history VARCHAR)");
        //create tables for arrayExpress
        stmt.execute("CREATE TABLE organism ("
                + "organismID INTEGER PRIMARY KEY NOT NULL,"
                + "name VARCHAR,"
                + "UNIQUE (name) ON CONFLICT IGNORE)");
        stmt.execute("CREATE TABLE organismpart ("
                + "organismpartID INTEGER PRIMARY KEY NOT NULL,"
                + "name VARCHAR,"
                + "UNIQUE (name) ON CONFLICT IGNORE)");
        //arrayexpress table with foreign references to organism, organismpart and gene
        stmt.execute("CREATE TABLE arrayexpress ("
                + "arrayexpressID INTEGER PRIMARY KEY NOT NULL,"
                + "upPvalue VARCHAR,"
                + "downPvalue VARCHAR,"
                + "upExperiment INTEGER,"
                + "downExperiment INTEGER,"
                + "nonDEExperiment INTEGER,"
                + "geneID INTEGER,"
                + "organismID INTEGER,"
                + "organismpartID INTEGER,"
                + " FOREIGN KEY (geneID) REFERENCES gene(geneID),"
                + " FOREIGN KEY (organismID) REFERENCES organism(organismID),"
                + " FOREIGN KEY (organismpartID) REFERENCES organismpart(organismpartID))");

        connection.commit();
        connection.setAutoCommit(true);
        print("Database structure for UniProt set up.");
    }

    // ------------------------------------------
    /**
     * Adds Query to Queryhistorytable
     *
     * @param querystring
     */
    @SuppressWarnings("empty-statement")
    public static void addQuery(String querystring) throws SQLException {
        querystring = querystring.replace("\"", "\\\"");
        writeQuery.setString(1, querystring);
        writeQuery.execute();

        // handle query amount
        //TODO: Test und ggf. ersetzten
        int maxQuery = ApplicationConfig.getMaxQuery();
        if (Math.random() <= 10 / maxQuery) {
            ResultSet rs = selectQuery.executeQuery();
            //skip newest querys
            for (int i = 0; rs.next() && i < maxQuery; i++);
            while (rs.next()) {
                int id = rs.getInt(1);
                deleteQuery.setInt(1, id);
                deleteQuery.execute();
            }
        }
    }

    public static ResultSet getQueryHistory() throws SQLException {
        return SQLiteDB.executeQuery("SELECT * FROM queryhistory ORDER BY id DESC LIMIT " + ApplicationConfig.getMaxQuery());
    }

    /**
     * Adds a Protein to the protein table.
     *
     * @param protein
     * @throws SQLException
     */
    public static void addProtein(Protein protein) throws SQLException {
        ApplicationConfig.setImportDetails("Adding Protein " + protein.getName() + " to Database");
        // check if this protein already exists
        readProtein.setString(1, protein.getUniProtID());
        ResultSet result = readProtein.executeQuery();
        if (result.next()) {
            printFailureMessage("Skipped Protein (" + protein.getName() + ").\n");
            return;
        }
        // add it otherwise
        // add Gene and get geneID
        int geneID = addGene(protein);

        // add Protein
        writeProtein.setString(1, protein.getUniProtID());
        writeProtein.setString(2, protein.getName());
        writeProtein.setInt(3, geneID);
        writeProtein.execute();

        // add Synonyms if there are any
        if (!protein.getGeneSynonyms().isEmpty())
            addSynonyms(protein.getGeneSynonyms(), geneID);

        // add Isoforms if there are any
        if (!protein.getIsoforms().isEmpty())
            addIsoforms(protein.getIsoforms(), protein.getUniProtID());

        printSuccessMessage("Added Protein (" + protein.getName() + ").\n");
    }

    /**
     * will add arrayexpressreference
     *
     * @param organismID organism to be referenced to
     * @param organismPartID organismpart of organism
     * @param geneID gene of organism
     * @param upPvalue pvalue of upexpression
     * @param downPvalue pvalue of downexpression
     * @return created arrayexpressid
     */
    private static void addArrayExpress(int organismID, int organismPartID, int geneID, String upPvalue, String downPvalue, int upExperiment, int downExperiment, int nonDEExperiment) throws SQLException {
        writeArrayExpress.setInt(1, organismID);
        writeArrayExpress.setInt(2, organismPartID);
        writeArrayExpress.setInt(3, geneID);
        writeArrayExpress.setString(4, upPvalue);
        writeArrayExpress.setString(5, downPvalue);
        writeArrayExpress.setInt(6, upExperiment);
        writeArrayExpress.setInt(7, downExperiment);
        writeArrayExpress.setInt(8, nonDEExperiment);
        writeArrayExpress.addBatch();
        //TODO: wieder rein?
        //   ResultSet rs = writeArrayExpress.getGeneratedKeys();
        //   rs.next();  //Initially the cursor is positioned before the first row
        //   ApplicationConfig.setImportDetails("Adding new ArrayExpress ( "+ rs.getInt(1) + " )  to database");
        //   return rs.getInt(1);
    }

    /**
     * will add organism
     *
     *
     * @param name
     *
     * @return
     * @throws SQLException
     */
    private static int addOrganism(String name) throws SQLException {
        ApplicationConfig.setImportDetails("Adding Organism " + name + " to Database");
        writeOrganism.setString(1, name);
        int organismID;
        if (writeOrganism.execute()) {
            ResultSet rs = writeOrganism.getGeneratedKeys();
            rs.next();  //Initially the cursor is positioned before the first row
            organismID = rs.getInt(1);
        }
        else {
            readOrganism.setString(1, name);
            organismID = readOrganism.executeQuery().getInt("organismID");
        }

        ApplicationConfig.setImportDetails("Organism " + name + " has " + organismID + " organismID in  Database");
        return organismID;
    }

    /**
     * Will add organismpart if it doesnt exist
     *
     * @param name
     * @return
     * @throws SQLException
     */
    private static int addOrganismPart(String name) throws SQLException {
        writeOrganismPart.setString(1, name);
        writeOrganismPart.executeUpdate();
        int organismpartID;
        if (writeOrganismPart.execute()) {
            ResultSet rs = writeOrganismPart.getGeneratedKeys();
            rs.next();  //Initially the cursor is positioned before the first row
            organismpartID = rs.getInt(1);
        }
        else {
            readOrganismPart.setString(1, name);
            organismpartID = readOrganismPart.executeQuery().getInt("organismpartID");
        }

        ApplicationConfig.setImportDetails("Organismpart " + name + " has " + organismpartID + " organismpartID in  Database");
        return organismpartID;
    }

    /**
     * Adds a Gene to the gene table.
     *
     * @param geneID
     * @param name
     * @param uniProtID
     * @throws SQLException
     */
    private static int addGene(Protein protein) throws SQLException {
        String gene = protein.getGene();
        String arrayExpressURL = protein.getArrayExpressUrl();
        ApplicationConfig.setImportDetails("Adding gene " + gene + " to Database");
        // if gene already exists find its geneID and return it
        readGene.setString(1, gene);
        ResultSet result = readGene.executeQuery();
        if (result.next()) {
            printFailureMessage("Skipped Gene (" + gene + ").");
            return result.getInt("geneID");
        }
        // else add the gene
        writeGene.setString(1, gene);
        writeGene.setString(2, arrayExpressURL);
        writeGene.execute();
        readGene.setString(1, gene);
        printSuccessMessage("Added Gene (" + gene + ").");
        int geneID = readGene.executeQuery().getInt("geneID");
        //and add arrayexpression of this gene, if gene isnt unknown
        if (!"unknown".equals(gene)) {
            //eigentlich erst hier die geneExpression anwerfen, beschleunigt den prozess!
            Iterator it = protein.getGeneExpression().getOrganisms().iterator();
            System.out.println("ORGANISMENZAHL DES GENES " + protein.getGeneExpression().getOrganisms().size());
            while (it.hasNext()) {
                Organism organism = (Organism) it.next();
                int organismID = addOrganism(organism.getName());
                Iterator itPart = organism.getOrganismParts().iterator();
                while (itPart.hasNext()) {
                    OrganismPart organismPart = (OrganismPart) itPart.next();
                    int organismPartID = addOrganismPart(organismPart.getName());
                    SQLiteDB.addArrayExpress(
                            organismID,
                            organismPartID,
                            geneID,
                            organismPart.getupPvalue(),
                            organismPart.getdownPvalue(),
                            organismPart.getupExperiment(),
                            organismPart.getdownExperiment(),
                            organismPart.getnonDEEexperiment());
                }
            }
            ApplicationConfig.setImportDetails("Adding all ArrayExpresses for gene at once");
            writeArrayExpress.executeBatch();
            ApplicationConfig.setImportDetails("Adding all ArrayExpresses for gene at once - done");
        }
        return geneID;
    }

    /**
     * Adds a Synonym to the synonym table.
     *
     * @param name
     * @param geneID
     * @throws SQLException
     */
    private static void addSynonyms(List<String> synonyms, int geneID) throws SQLException {
        ApplicationConfig.setImportDetails("Adding Synonyms for geneID " + geneID + " to Database");
        for (String synonym : synonyms) {
            // check if there already is a synonym with this name and geneID
            readSynonym.setInt(1, geneID);
            readSynonym.setString(2, synonym);
            ResultSet result = readSynonym.executeQuery();
            // add it if there isn't
            if (!result.next()) {
                writeSynonym.setString(1, synonym);
                writeSynonym.setInt(2, geneID);
                writeSynonym.execute();
                printSuccessMessage("Added Synonym (" + synonym + ").");
            }
            else
                printFailureMessage("Skipped Synonym (" + synonym + ").");
        }
    }

    private static void addIsoforms(List<String> isoforms, String uniProtID) throws SQLException {
        ApplicationConfig.setImportDetails("Adding IsoForms for uniProtID " + uniProtID + " to Database");
        for (String isoform : isoforms) {
            // check if there already is an isoform with this name and uniProtID
            readIsoform.setString(1, uniProtID);
            readIsoform.setString(2, isoform);
            ResultSet result = readIsoform.executeQuery();
            // add if there isn't
            if (!result.next()) {
                writeIsoform.setString(1, isoform);
                writeIsoform.setString(2, uniProtID);
                writeIsoform.execute();
                printSuccessMessage("Added Isoform (" + isoform + ").");
            }
            else
                printFailureMessage("Skipped Isoform (" + isoform + ").");
        }
    }

    // ------------------------------------------
    /**
     * Returns complete Protein object as found in database, null if it doesn't
     * exist
     *
     * @param proteinName
     * @return
     * @throws SQLException
     */
    public static Protein getProteinFromName(String proteinName) throws SQLException {
        ResultSet result;
        String uniProtID, gene, arrayExpressURL;
        List<String> synonyms = new ArrayList<String>();
        List<String> isoforms = new ArrayList<String>();
        int geneID;
        // uniProtID
        getUniProtID.setString(1, proteinName);
        result = getUniProtID.executeQuery();
        if (!result.next())
            return null;
        uniProtID = result.getString("uniProtID");

        // gene & arrayExpressURL
        readProtein.setString(1, uniProtID);
        result = readProtein.executeQuery();
        if (!result.next())
            return null;
        geneID = result.getInt("geneID");

        getGene.setInt(1, geneID);
        result = getGene.executeQuery();
        if (!result.next())
            return null;
        gene = result.getString("name");
        arrayExpressURL = result.getString("arrayExpressURL");

        // synonyms
        getSynonyms.setInt(1, geneID);
        result = getSynonyms.executeQuery();
        while (result.next())
            synonyms.add(result.getString("name"));

        // isoforms
        getIsoforms.setString(1, uniProtID);
        result = getIsoforms.executeQuery();
        while (result.next())
            isoforms.add(result.getString("name"));

        Protein protein = new Protein(proteinName, gene, uniProtID, arrayExpressURL, synonyms, isoforms);
        return protein;
    }

    /**
     * Returns complete Protein object as found in database, null if it doesn't
     * exist
     *
     * @param uniProtID
     * @return
     * @throws SQLException
     */
    public static Protein getProtein(String uniProtID) throws SQLException {
        readProtein.setString(1, uniProtID);
        ResultSet result = readProtein.executeQuery();
        if (result.next())
            return getProteinFromName(result.getString("name"));
        return null;
    }

    // ------------------------------------------
    /**
     * Exectues a specified SQL-Query and returns its ResultSet.
     *
     * @param query
     * @return
     * @throws SQLException
     */
    public static ResultSet executeQuery(String query) throws SQLException {
        return stmt.executeQuery(query);
    }

    /**
     * Checks for integral structure of database by looking for tables "gene",
     * "protein" and "synonym"
     *
     * @return true if all three tables exist
     * @throws SQLException
     */
    private static boolean hasStructure() throws SQLException {
        //TODO: ergaenzen fuer neue tabellen?
        String[] tableNames = {"gene", "protein", "synonym"};
        ResultSet result;
        for (String name : tableNames) {
            result = stmt.executeQuery("SELECT COUNT(*) FROM sqlite_master WHERE type = 'table' AND name = '" + name + "'");
            if (result.getInt(1) <= 0)
                return false;
        }
        return true;
    }

    private static void print(String message) {
        if (shouldPrint)
            System.out.println(message);
    }

    private static void printSuccessMessage(String message) {
        if (shouldPrintSuccess)
            System.out.println("- " + message);
    }

    private static void printFailureMessage(String message) {
        if (shouldPrintFailure)
            System.out.println("- " + message);
    }
    // ------------------------------------------
    private static boolean shouldPrint = true;
    private static boolean shouldPrintSuccess = true;
    private static boolean shouldPrintFailure = true;
    private static Connection connection = null;
    private static Statement stmt = null;
    private static PreparedStatement readProtein;
    private static PreparedStatement readGene;
    private static PreparedStatement readSynonym;
    private static PreparedStatement readIsoform;
    private static PreparedStatement writeProtein;
    private static PreparedStatement writeOrganism;
    private static PreparedStatement readOrganism;
    private static PreparedStatement writeOrganismPart;
    private static PreparedStatement readOrganismPart;
    private static PreparedStatement writeGene;
    private static PreparedStatement writeSynonym;
    private static PreparedStatement writeIsoform;
    private static PreparedStatement getUniProtID;
    private static PreparedStatement getGene;
    private static PreparedStatement getSynonyms;
    private static PreparedStatement getIsoforms;
    private static PreparedStatement writeQuery;
    private static PreparedStatement selectQuery;
    private static PreparedStatement deleteQuery;
    private static PreparedStatement writeArrayExpress;
    private static PreparedStatement readArrayExpress;

    // ------------------------------------------
    public void contextInitialized(ServletContextEvent sce) {
    }

    public void contextDestroyed(ServletContextEvent sce) {
        try {
            if (connection != null)
                connection.close();
        } catch (SQLException ex) {
            System.out.println("Error: " + ex.getMessage());
        }
        ApplicationConfig.unmountDatabase();
    }
}
